package org.teremail.mailbox;

import static org.teremail.schema.DBTypes.INT4;
import static org.teremail.schema.DBTypes.VARCHAR;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.UUID;

import javax.sql.DataSource;

import org.teremail.common.Log;
import org.teremail.mailbox.Folder.SortKey;
import org.teremail.schema.Dialect;
import org.teremail.schema.Schema;
import org.teremail.schema.Table;
import org.teremail.schema.TableBuilder;
import org.teremail.service.Serviceable;
import org.teremail.service.ServiceAdapter;
import org.teremail.service.ServiceRegistrar;
import org.teremail.util.DB;

import com.google.common.base.Joiner;
import com.google.inject.Inject;

public class DBMailboxDAO implements MailboxDAO, Serviceable {

    private static final Log log = Log.getLog(DBMailboxDAO.class);
    public static final Table MAILBOX_TABLE;
    public static final Table FOLDER_TABLE;
    public static final Table ENTRY_TABLE;
    
    static {
        TableBuilder mailboxB = new TableBuilder("MAILBOX");
        mailboxB.column("ID", VARCHAR(40)).primaryKey();
        mailboxB.column("USERNAME", VARCHAR(255));
        mailboxB.addUnique("USERNAME");
        MAILBOX_TABLE = mailboxB.build();
        
        TableBuilder folderB = new TableBuilder("FOLDER");
        folderB.column("ID", VARCHAR(40)).primaryKey();
        folderB.column("PATH", VARCHAR(255));
        folderB.column("MAILBOX_ID", VARCHAR(40));
        folderB.column("VERSION", INT4());
        folderB.addUnique("PATH", "MAILBOX_ID");
        FOLDER_TABLE = folderB.build();
        
        TableBuilder entryB = new TableBuilder("ENTRY");
        entryB.column("UID", INT4()).primaryKey();
        entryB.column("FOLDER_ID", VARCHAR(40)).primaryKey();
        entryB.column("MESSAGE_ID", VARCHAR(40));
        ENTRY_TABLE = entryB.build();
    }

    private final DataSource ds;
    private final Dialect dialect;
    
    @Inject
    public DBMailboxDAO(DataSource ds, Dialect dialect) {
        this.ds = ds;
        this.dialect = dialect;
    }
    
    public void addMessage(FolderDO fdo, String messageId) throws FolderNotExistsException {
        
        final String insert = "INSERT INTO entry (uid, folder_id, message_id) VALUES (?, ?, ?)";
        int uid = getNextUid(fdo);
        Connection cn = null;
        PreparedStatement ps = null;
        
        try {
            cn = ds.getConnection();
            ps = cn.prepareStatement(insert);
            ps.setInt(1, uid);
            ps.setString(2, fdo.getId());
            ps.setString(3, messageId);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(ps, cn);
        }
    }

    /**
     * TODO: This needs to run in a seperate transaction.
     * 
     * @param fdo
     * @return
     * @throws FolderNotExistsException 
     */
    private int getNextUid(FolderDO fdo) throws FolderNotExistsException {
        final String select = "SELECT version FROM folder WHERE id = ? FOR UPDATE";
        final String update = "UPDATE folder SET version = version + 1 WHERE id = ?";
        
        Connection cn = null;
        PreparedStatement psSelect = null;
        PreparedStatement psUpdate = null;
        ResultSet rs = null;

        try {
            log.debug("SQL: " + select);
            int version;
            cn = ds.getConnection();
            psSelect = cn.prepareStatement(select);
            psSelect.setString(1, fdo.getId());
            rs = psSelect.executeQuery();
            
            if (rs.next()) {
                version = rs.getInt(1);
            } else {
                throw new FolderNotExistsException(fdo.getMailbox().getUser(), fdo.getName());
            }
            
            log.debug("SQL: " + update);
            psUpdate = cn.prepareStatement(update);
            psUpdate.setString(1, fdo.getId());
            psUpdate.executeUpdate();
            
            return version;
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, psSelect, null);
            DB.close(psUpdate, cn);
        }
        
    }

    public List<Long> getUids(FolderDO fdo) {
        String select = "SELECT uid FROM entry WHERE folder_id = ?";
        
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            List<Long> uids = new ArrayList<Long>();
            cn = ds.getConnection();
            ps = cn.prepareStatement(select);
            ps.setString(1, fdo.getId());
            rs = ps.executeQuery();
            while (rs.next()) {
                uids.add(rs.getLong(1));
            }
            
            return uids;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
        
    }
    
    
    public void remove(MailboxDO mailbox, Path path)
            throws MailboxNotExistsException, FolderNotExistsException {
        // TODO Auto-generated method stub

    }

    public void renameFolder(MailboxDO mailbox, Path fromPath, Path toPath)
            throws MailboxNotExistsException, FolderExistsException,
            FolderNotExistsException {
        // TODO Auto-generated method stub

    }

    public MailboxDO findMailbox(String user) throws MailboxNotExistsException {
        String sql = "SELECT id, username FROM mailbox where username = ?";
        
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            log.debug("SQL %s", sql);
            cn = ds.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setString(1, user);
            rs = ps.executeQuery();
            
            if (rs.next()) {
                String id = rs.getString(1);
                return new MailboxDO(id, user);
            } else {
                throw new MailboxNotExistsException(user);
            }
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }

    public MailboxDO create(String user) throws MailboxExistsException {
        String s = "INSERT INTO mailbox (id, username) VALUES (?, ?)";
        
        Connection cn = null;
        PreparedStatement ps = null;
        
        try {
            log.debug("SQL: %s", s);
            cn = ds.getConnection();
            ps = cn.prepareStatement(s);
            String id = UUID.randomUUID().toString();
            ps.setString(1, id);
            ps.setString(2, user);
            ps.executeUpdate();
            return new MailboxDO(id, user);
        } catch (SQLException e) {
            if (dialect.isDuplicateKey(e.getSQLState())) {
                throw new MailboxExistsException(user);
            } else {
                throw new RuntimeException(String.valueOf(e.getSQLState()), e);
            }
        } finally {
            DB.close(ps, cn);
        }
    }

    public FolderDO findFolder(MailboxDO mailbox, Path path)
            throws MailboxNotExistsException, FolderNotExistsException {
        
        String sql = "SELECT id, path FROM FOLDER WHERE mailbox_id = ? AND path = ? ";
        
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        String user = mailbox.getUser();
        String pathStr = path.toString();
        
        try {
            log.info("SQL: %s", sql);
            cn = ds.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setString(1, mailbox.getId());
            ps.setString(2, pathStr);
            rs = ps.executeQuery();
            if (rs.next()) {
                String id = rs.getString(1);
                return new FolderDO(id, path, 1, mailbox);
            } else {
                throw new FolderNotExistsException(user, pathStr);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }

    public FolderDO save(FolderDO folder) throws FolderExistsException {
        String sql = "INSERT INTO folder (id, path, version, mailbox_id) VALUES (?, ?, 1, ?)";
        
        Connection cn = null;
        PreparedStatement ps = null;
        
        try {
            log.debug("SQL: %s", sql);
            cn = ds.getConnection();
            ps = cn.prepareStatement(sql);
            String id = UUID.randomUUID().toString();
            ps.setString(1, id);
            ps.setString(2, folder.getPath().toString());
            ps.setString(3, folder.getMailbox().getId());
            ps.executeUpdate();
            
            return new FolderDO(id, folder);
        } catch (SQLException e) {
            if (dialect.isDuplicateKey(e.getSQLState())) {
                throw new FolderExistsException(folder.getMailbox().getUser(), 
                        folder.getPath().toString());
            } else {
                throw new RuntimeException(e);
            }
        } finally {
            DB.close(ps, cn);
        }
    }
    
    
    public List<FolderDO> getFolders(MailboxDO mailbox, Path root) {
        String sql = "SELECT id, path, version FROM folder WHERE mailbox_id = ? AND path like ?";
        String path = root.toString() + "%";
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            log.debug(sql);
            cn = ds.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setString(1, mailbox.getId());
            ps.setString(2, path);
            List<FolderDO> folders = new ArrayList<FolderDO>();
            rs = ps.executeQuery();
            while (rs.next()) {
                String id = rs.getString(1);
                Path p = Path.path(rs.getString(2));
                int version = rs.getInt(3);
                folders.add(new FolderDO(id, p, version, mailbox));
            }
            return folders;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }

    
    public List<EntryDO> getEntries(FolderDO fdo) {
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT uid, message_id FROM entry WHERE folder_id = ?";
            log.debug(sql);
            cn = ds.getConnection();
            ps = cn.prepareStatement(sql);
            ps.setString(1, fdo.getId());
            
            List<EntryDO> entries = new ArrayList<EntryDO>(128);
            rs = ps.executeQuery();
            while (rs.next()) {
                int uid = rs.getInt(1);
                String messageId = rs.getString(2);
                entries.add(new EntryDO(uid, messageId));
            }
            
            return entries;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }
    
    
    public List<EntryDO> getEntries(FolderDO fdo, int pageNo, int pageSize, SortKey...keys) {
        
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String orderBy = getOrderyBy(keys);
        
        try {
            cn = ds.getConnection();
            List<EntryDO> edo = new ArrayList<EntryDO>();
            
            if (dialect.isPagingSupported()) {
                String select = dialect.createPagedQuery("entry.uid, entry.message_id", 
                        "entry JOIN entity ON entry.message_id = entity.id", 
                        "folder_id = ?", orderBy, pageNo, pageSize);
                ps = cn.prepareStatement(select);
                ps.setString(1, fdo.getId());
                rs = ps.executeQuery();
                
                while (rs.next()) {
                    int uid = rs.getInt(1);
                    String messageId = rs.getString(2);
                    edo.add(new EntryDO(uid, messageId));
                }
            } else {
                String select = "SELECT entry.uid, entry.message_id FROM entry JOIN entity ON entry.message_id = entity.id WHERE entry.folder_id = ? ORDER BY " + orderBy;
                log.debug("SQL: %s", select);
                ps = cn.prepareStatement(select);
                ps.setString(1, fdo.getId());
                rs = ps.executeQuery();
                
                int i = 0;
                int min = pageNo * pageSize;
                int max = ((pageNo + 1) * pageSize) - 1;
                while (rs.next()) {
                    if (i >= min && i <= max) {
                        int uid = rs.getInt(1);
                        String messageId = rs.getString(2);
                        edo.add(new EntryDO(uid, messageId));
                    } else if (i > max) {
                        break;
                    }
                    i++;
                }
            }
            
            return Collections.unmodifiableList(edo);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }
    
    public EntryDO getEntry(FolderDO f, int uid) throws EntryNotExistsException {
        Connection cn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            cn = ds.getConnection();
            ps = cn.prepareStatement("SELECT message_id FROM entry WHERE folder_id = ? AND uid = ?");
            ps.setString(1, f.getId());
            ps.setInt(2, uid);
            rs = ps.executeQuery();
            
            if (rs.next()) {
                String messageId = rs.getString(1);
                return new EntryDO(uid, messageId);
            } else {
                throw new EntryNotExistsException(f.getId(), uid);
            }
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(rs, ps, cn);
        }
    }
    
    private String getOrderyBy(SortKey[] keys) {
        if (keys.length == 0) {
            return "uid";
        }
        
        String[] cols = new String[keys.length];
        
        for (int i = 0; i < cols.length; i++) {
            SortKey key = keys[i];
            switch (key) {
            case DATE:
                cols[i] = DBMessageDAO.ENTITY_TABLE.getName() + ".SENT_DATE";
                break;
            case FROM:
                cols[i] = DBMessageDAO.ENTITY_TABLE.getName() + ".FROM_ADDRESS";
                break;
            case UID:
                cols[i] = ENTRY_TABLE.getName() + ".UID";
                break;
            default:
                cols[i] = DBMessageDAO.ENTITY_TABLE.getName() + "." + key.name();
            }
        }
        
        return Joiner.on(", ").join(cols);
    }

    public DataSource getDataSource() {
        return ds;
    }
    
    // ==================
    // Service Management
    // ==================
    
    public void init() {
        Schema.createIfNotExists(ds, MAILBOX_TABLE, dialect);
        Schema.createIfNotExists(ds, FOLDER_TABLE, dialect);
        Schema.createIfNotExists(ds, ENTRY_TABLE, dialect);
    }

    @Inject
    public void registerService(ServiceRegistrar serviceRegistrar) {
        serviceRegistrar.register(new ServiceAdapter(){
            @Override
            public void start() throws Exception {
                init();
            }
        });
    }
}
